/**
 * 
 */
package bookshop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bookshop.dao.CustomerDao;
import bookshop.domain.Customer;
import bookshop.exception.DataAccessException;
import bookshop.service.helper.CustomerQueryHelper;
import bookshop.utils.DBUtils;

/**
 * @author Administrator
 *
 */
public class CustomerDaoJDBCImpl implements CustomerDao {

	private static final String ADD_CUSTOMER="insert into customer values(?,?,?,?,?)";
	private static final String GET_CUSTOMERBYID="select * from customer where customer_id=?";

	private static final String UPDATE_MOODLEVEL_BYCOUNT = "update customer set mood_level=? where customer_id=?";

	private static final String SQL_LOADALL ="select *from customer order by customer_id desc";
	private static final String SQL_UPDATE ="update customer set customer_pwd=?,customer_name=? where customer_id=?";
	
	private String generateSQL(CustomerQueryHelper helper){
		
		String sql = "select * from customer where 1=1";
		
		if(helper.getQryCustomerId()!=null)
			sql += " and customer_id like '%" + helper.getQryCustomerId() +"%' ";
		
		if(helper.getQryCustomerName()!=null)
			sql += " and customer_name like '%" + helper.getQryCustomerName() +"%' ";
		
		if(helper.getQryMoodLevel()!=null)
			sql += " and mood_level like '%" + helper.getQryMoodLevel() +"%' ";
		
		if(helper.getQryCustomerStatus()!=null)
			sql += " and customer_status like '%" + helper.getQryCustomerStatus() +"%' ";
		
		sql += " order by customer_id";
			
		
		return sql;
	}
	
	
	public void addCustomer(Customer customer) {
		// TODO Auto-generated method stub
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		
		try {
			ps=ct.prepareStatement(ADD_CUSTOMER);
			ps.setString(1,customer.getCustomerId());
			ps.setString(2, customer.getCustomerPwd());
			ps.setString(3, customer.getCustomerName());
			ps.setString(4,customer.getMoodLevel());
			ps.setString(5, customer.getCustomerStatus());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DataAccessException("对不起，这个账号已经被注册过！");
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
	}

	
	public Customer getCustomerById(String customerId) {
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs=null;
		Customer customer=null;
		
		try {
			ps=ct.prepareStatement(GET_CUSTOMERBYID);
			ps.setString(1, customerId);
			rs=ps.executeQuery();
			
			if(rs.next()){
				customer = new Customer();
				customer.setCustomerId(rs.getString("customer_id"));
				customer.setCustomerPwd(rs.getString("customer_pwd"));
				customer.setCustomerName(rs.getString("customer_name"));
				customer.setMoodLevel(rs.getString("mood_level"));
				customer.setCustomerStatus(rs.getString("customer_status"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		return customer;
	}

	
	public void updateMoodLevelByCount(Long count,String custId){
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		String ml = null;
		if(count <=3){
			ml = "D";
		}else if(count >3&&count<=10){
			ml = "C";
		}else if(count >10&&count<=15){
			ml = "B";
		}else{
			ml = "A";
		}
		
		try {
			ps=ct.prepareStatement(UPDATE_MOODLEVEL_BYCOUNT);
			ps.setString(1, ml);
			ps.setString(2, custId);
			ps.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
		
		
		
		
	}



	
	public List<Customer> loadall() {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Customer> customers =null;
		
		try {
			ps=ct.prepareStatement(SQL_LOADALL);
			rs=ps.executeQuery();
			while(rs.next()){
				if(customers==null){
					customers = new ArrayList<Customer>();
				}
				Customer customer = new Customer();
				
				customer.setCustomerId(rs.getString("customer_id"));
				customer.setCustomerPwd(rs.getString("customer_pwd"));
				customer.setCustomerName(rs.getString("customer_name"));
				customer.setMoodLevel(rs.getString("mood_level"));
				customer.setCustomerStatus(rs.getString("customer_status"));
				
				customers.add(customer);
			}
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		return customers;
	}




	
	public List<Customer> getCustomerByCondition(CustomerQueryHelper helper) {
		String sql=this.generateSQL(helper);
		
//		System.out.println(sql);
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Customer customer =null;
		List<Customer> customers =new ArrayList<Customer>();
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				customer =new Customer();
				customer.setCustomerId(rs.getString("customer_id"));
				customer.setCustomerPwd(rs.getString("customer_pwd"));
				customer.setCustomerName(rs.getString("customer_name"));
				customer.setMoodLevel(rs.getString("mood_level"));
				customer.setCustomerStatus(rs.getString("customer_status"));
				
				customers.add(customer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询用户信息类别获取失败！");
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		return customers;
	}


	
	public Long countCustomerByCondition(CustomerQueryHelper helper) {
		String sql=this.generateSQL(helper);
		
//		System.out.println(sql);
		
		sql =sql.replace("*", "count(*) as total");
		sql =sql.substring(0,sql.indexOf("order by"));
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Long count = 0L;
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()){
				count = rs.getLong("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		return count;
	}



	public List<Customer> getScopedCustomers(CustomerQueryHelper helper,
			int startIndex, int fetchSize) {
		
		String sql = this.generateSQL(helper);
		sql += " limit " + startIndex + "," + fetchSize;
		
//		System.out.println(sql);
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Customer customer =null;
		
		List<Customer> customers =new ArrayList<Customer>();
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				customer =new Customer();
				customer.setCustomerId(rs.getString("customer_id"));
				customer.setCustomerPwd(rs.getString("customer_pwd"));
				customer.setCustomerName(rs.getString("customer_name"));
				customer.setMoodLevel(rs.getString("mood_level"));
				customer.setCustomerStatus(rs.getString("customer_status"));
				
				customers.add(customer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询用户信息类别获取失败！");
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		return customers;
		
	}


	
	public void updateCustomer(Customer customer) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		
		try {
			ps=ct.prepareStatement(SQL_UPDATE);
			ps.setString(1, customer.getCustomerPwd());
			ps.setString(2, customer.getCustomerName());
			ps.setString(3, customer.getCustomerId());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, null);
	    }
		
	}

}
